﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for PostingExhibitionDAO
/// </summary>
public class PostingExhibitionDAO : DBAccess, IPostingExhibition
{

    #region sql_commands
    private string SQL_SELECT_BY_ID = "select Status from PostingExhibition where PostingID = @postingID";
    private string SQL_INSERT = "insert into PostingExhibition values(@exhibitionId, @postingId, @status)";
    private string SQL_UPDATE = "update PostingExhibition set ExhibitionID = @exhibitionId, Status = @status where PostingID = @postingId";
    private string SQL_UPDATE_STATUS = "update PostingExhibition set Status = @status where PostingID = @postingId";
    private string SQL_DELETE = "delete from PostingExhibition where @PostingId = postingId";
    private string SQL_SELECT_POSTING_EXHIBITED = "SELECT post_e.PostingID, post.PostingName, post.Description, post.Price, post.Quotations, [user].Fullname, post_e.Status, e.ExhibitionName" 
        + "FROM Postings AS post JOIN PostingExhibition as post_e ON post.PostingID = post_e.PostingID JOIN Exhibition as e ON post_e.ExhibitionID = e.ExhibitionID" 
        + "JOIN Sales as s ON post.PostingID = s.PostingID JOIN UserInfomations [user] ON s.CustomerID = [user].UserID where e.ExhibitionID = @exhibitionId";
    string message;
    #endregion

    public PostingExhibitionDAO()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    #region IPostingExhibition Members
    /// <summary>
    /// Insert PostingExhibitionInfo
    /// </summary>
    /// <returns>IList</returns>
    /// 
    public int Insert(PostingExhibitionInfo pei)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_INSERT, conn);
            cmd.Parameters.AddWithValue("@exhibitionId", pei.ExhibitionID);
            cmd.Parameters.AddWithValue("@postingId", pei.PostingID);
            cmd.Parameters.AddWithValue("@status", pei.Status);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }



    public string GetStatusById(Int64 postingId)
    {
        string status = "";
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_SELECT_BY_ID, conn);
            cmd.Parameters.AddWithValue("@postingID", postingId);
            if (Connect())
            {
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                   status = reader.GetString(0);      
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return status;
    }





    /// <summary>
    /// Update 
    /// </summary>
    /// <returns>IList</returns>
    public int Update(PostingExhibitionInfo pei)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_UPDATE, conn);
            cmd.Parameters.AddWithValue("@exhibitionId", pei.ExhibitionID);
            cmd.Parameters.AddWithValue("@postingId", pei.PostingID);
            cmd.Parameters.AddWithValue("@status", pei.Status);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }
           
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }


    public int UpdateStatus(PostingExhibitionInfo pei)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_UPDATE_STATUS, conn);
            cmd.Parameters.AddWithValue("@postingId", pei.PostingID);
            cmd.Parameters.AddWithValue("@status", pei.Status);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }

        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }

    public int Delete(int id)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_DELETE, conn);
            cmd.Parameters.AddWithValue("@postingId", id);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;

    }      


    public DataSet SelectPostingExhibition(int id)
    {
        DataSet ds = new DataSet();
        SqlDataAdapter adapter = new SqlDataAdapter(SQL_SELECT_POSTING_EXHIBITED, conn);
        adapter.SelectCommand.Parameters.AddWithValue("@exhibitionId", id);
        adapter.Fill(ds, "postingExhibited");
        return ds;
    }

    #endregion
}